﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ncp.Business
{
    public class CommentInfoBll
    {
        /// <summary>
        /// 得到评论信息表
        /// </summary>
        public static DataTable GetCommentInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select * from bi_CommentInfo";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }
        public static DataTable GetCommentInfoTable(string field,string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select "+field+" from bi_CommentInfo";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }
        /// <summary>
        /// 得到评论记录数
        /// </summary>
        public static int GetCommentInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_CommentInfo_v";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到新闻信息表
        /// </summary>
        public static DataTable GetCommentInfoTable(string fieldList, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            string sqlStr = "select " + fieldList + " from bi_CommentInfo_v where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetCommentInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_CommentInfo_v", pcFieldList, "Comment_Id", pcOrderBy, pcWhere);
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetCommentInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, string dicWhere, string pDataBase)
        {
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_CommentInfo_v", pcFieldList, "Comment_Id", pcOrderBy, dicWhere);
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改评论状态
        /// </summary>
        public static int UpdateCommentStatus(string Comment_Id, string Comment_Status, string pDataBase)
        {
            string sqlStr = "update bi_CommentInfo set Comment_Status='" + Comment_Status + "' where Comment_Id='" + Comment_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改评论信息
        /// </summary>
        public static int UpdateCommentInfo(Dictionary<string, object> dicComment, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.bi_CommentInfo " +
                            "SET Product_Id = '" + dicComment["product_id"].ToString() + "', " +
                            "Client_Id =  '" + dicComment["client_id"].ToString() + "', " +
                            "Comment_Note =  '" + dicComment["comment_note"].ToString() + "'," +
                            "Comment_Status =  '" + dicComment["comment_status"].ToString() + "'," +
                            "where Comment_Id='" + dicComment["comment_id"].ToString() + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 增加评论信息
        /// </summary>
        public static int InsertCommentInfo(Dictionary<string, object> dicComment, string pDataBase)
        {
            string sqlStr = "INSERT INTO dbo.bi_CommentInfo (Product_Id, Client_Id, Comment_Note, Comment_Date, Comment_Status)" +
                            "VALUES (@Product_Id, @Client_Id, @Comment_Note, getdate(), @Comment_Status)";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@Product_Id", dicComment["product_id"]),
                                           new SqlParameter("@Client_Id", dicComment["client_id"]),
                                           new SqlParameter("@Comment_Note", dicComment["comment_note"]),
                                           new SqlParameter("@Comment_Status", dicComment["comment_status"])
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 删除评论
        /// </summary>
        public static int DeleteCommentInfo(string Comment_Id, string pDataBase)
        {
            string sqlStr = "delete from bi_CommentInfo where Comment_Id in(" + Comment_Id + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        public static DataTable GetCommentById(string Comment_Id, string pDataBase)
        {
            string sqlStr = "select * from bi_CommentInfo where Comment_Id=@Comment_Id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@Comment_Id", Comment_Id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }
    }
}
